#!/usr/local/bin/python
# -*- coding: utf-8 -*-



from sqlalchemy import Column, Integer, Float, and_, Text, Date
from sqlalchemy.orm import declarative_base

import init
from init import session
import toolkit

Base = declarative_base()

class BalanceSheetAlchemy(Base):
    '''
    类功能：资产负债表数据库映射类
    '''

    __tablename__ = "balance_sheet"

    id = Column(Integer, primary_key=True, autoincrement=True, comment="数据表id值")
    date_time = Column(Date, comment="数字格式的年份和月份时间")
    year = Column(Integer, comment="数据归属年份")
    month = Column(Integer, comment="数据归属月份")
    category = Column(Text, comment="资产负债表的类目")
    data_type = Column(Text, comment="标记属于[资产]或[权属]")
    path = Column(Text, comment="资产负债表类目所属路径")
    start_value = Column(Float, comment="月初金额")
    end_value = Column(Float, comment="月末金额")


    def __init__(self,
                 year=2022,
                 month=1,
                 category="",
                 dataType="",
                 path="",
                 startValue=0.0,
                 endValue=0.0
                 ):
        self.date_time = toolkit.setTargetDate(year, month)
        self.year = year
        self.month = month
        self.category = category
        self.data_type = dataType
        self.path = path
        self.start_value = startValue
        self.end_value = endValue

    def AddBalanceSheetItem(self):
        '''
        函数功能：添加一条数据
        :return:
        '''
        session.add(self)
        session.commit()

    def UpdateBalanceSheetItem(self):
        '''
        函数功能；更新一条数据
        :return:
        '''

    def SearchBalanceSheetByMonthCategory(self, year, month, category):
        '''
        函数功能：以时间和资产负债表科目为条件进行查询
        :param year:
        :param month:
        :return:
        '''

        balanceSheetInfo = session.query(BalanceSheetAlchemy).filter(and_(BalanceSheetAlchemy.year == year,
                                                                        BalanceSheetAlchemy.month == month,
                                                                        BalanceSheetAlchemy.category == category,
                                                                          )).all()

        return balanceSheetInfo

    def SearchBalanceSheetByMonthDataType(self, year, month, dataType):
        '''
        函数功能：以时间和数据类型为条件进行查询
        :param year:
        :param month:
        :return:
        '''

        balanceSheetInfo = session.query(BalanceSheetAlchemy).filter(and_(BalanceSheetAlchemy.year == year,
                                                                        BalanceSheetAlchemy.month == month,
                                                                        BalanceSheetAlchemy.data_type == dataType,
                                                                          )).all()

        return balanceSheetInfo

    def ShowData(self):
        '''
        函数功能：展示读取出来的数据
        :return:
        '''
        pass

    def DelBalanceSheetByMonth(self, year, month):
        '''
        函数功能，删除指定月份的张博数据
        :param year:
        :param month:
        :return:
        '''

        deleteBalanceSheetInfo = session.query(BalanceSheetAlchemy).filter(and_(BalanceSheetAlchemy.year==year,
                                                                     BalanceSheetAlchemy.month==month))
        deleteBalanceSheetInfo.delete()
        session.commit()

    def UpdateAllDatetimeDataTemp(self):
        '''
        函数功能：临时更新收支表数据,用于填充date_time字段的数据
        :return:
        '''

        balanceSheetAlchemyList = session.query(BalanceSheetAlchemy).filter().all()

        for i in range(len(balanceSheetAlchemyList)):

            balanceSheetAlchemyItem = balanceSheetAlchemyList[i]
            balanceSheetAlchemyItem.date_time = toolkit.setTargetDate(balanceSheetAlchemyItem.year,
                                                                      balanceSheetAlchemyItem.month)

        session.commit()
        return


def main():

    balanceSheetAlchemy = BalanceSheetAlchemy()
    balanceSheetAlchemy.UpdateAllDatetimeDataTemp()

    '''
    balanceSheetAlchemy = BalanceSheetAlchemy(year = 2022,
                                          month = 7,
                                          category = "银行存款",
                                          dataType = "",
                                          path="资产->自有->货币->银行存款",
                                          startValue=0.0,
                                          endValue=0.0
                                          )

    balanceSheetAlchemy.AddBalanceSheetItem()
    '''

#创建数据库表
BalanceSheetAlchemy.metadata.create_all(init.engine)
if __name__ == '__main__':
    main()